/*
 * Copyright 2015, The Querydsl Team (http://www.querydsl.com/team)
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.querydsl.sql;

import com.querydsl.core.Target;
import com.querydsl.sql.ddl.CreateTableClause;
import com.querydsl.sql.ddl.DropTableClause;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import org.hsqldb.types.Types;

/**
 * @author tiwe
 */
public final class Connections {

  public static final int TEST_ROW_COUNT = 100;

  private static ThreadLocal<Connection> connHolder = new ThreadLocal<Connection>();

  private static ThreadLocal<Target> targetHolder = new ThreadLocal<Target>();

  private static ThreadLocal<Configuration> configurationHolder = new ThreadLocal<Configuration>();

  // datetest
  private static final String CREATE_TABLE_DATETEST = "create table DATE_TEST(DATE_TEST date)";

  // survey
  private static final String CREATE_TABLE_SURVEY =
      "create table SURVEY(ID int auto_increment, NAME varchar(30), NAME2 varchar(30))";

  // test
  private static final String CREATE_TABLE_TEST = "create table TEST(NAME varchar(255))";

  // timetest
  private static final String CREATE_TABLE_TIMETEST = "create table TIME_TEST(TIME_TEST time)";

  // employee
  private static final String INSERT_INTO_EMPLOYEE =
      """
      insert into EMPLOYEE \
      (ID, FIRSTNAME, LASTNAME, SALARY, DATEFIELD, TIMEFIELD, SUPERIOR_ID) \
      values (?,?,?,?,?,?,?)\
      """;

  private static final String INSERT_INTO_TEST_VALUES = "insert into TEST values(?)";

  private static ThreadLocal<Statement> stmtHolder = new ThreadLocal<Statement>();

  private static boolean db2Inited,
      derbyInited,
      sqlServerInited,
      h2Inited,
      hsqlInited,
      mysqlInited,
      cubridInited,
      oracleInited,
      postgresqlInited,
      sqliteInited,
      teradataInited,
      firebirdInited;

  public static void close() throws SQLException {
    if (stmtHolder.get() != null) {
      stmtHolder.get().close();
    }
    if (connHolder.get() != null) {
      connHolder.get().close();
    }
  }

  public static Connection getConnection() {
    return connHolder.get();
  }

  public static Target getTarget() {
    return targetHolder.get();
  }

  public static Configuration getConfiguration() {
    return configurationHolder.get();
  }

  public static void initConfiguration(SQLTemplates templates) {
    configurationHolder.set(new Configuration(templates));
  }

  private static Connection getDB2() throws SQLException, ClassNotFoundException {
    Class.forName("com.ibm.db2.jcc.DB2Driver");
    String url = "jdbc:db2://localhost:50000/sample";
    return DriverManager.getConnection(url, "db2inst1", "a3sd!fDj");
  }

  private static Connection getDerby() throws SQLException, ClassNotFoundException {
    Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
    String url = "jdbc:derby:target/demoDB;create=true";
    return DriverManager.getConnection(url, "", "");
  }

  private static Connection getFirebird() throws SQLException, ClassNotFoundException {
    Class.forName("org.firebirdsql.jdbc.FBDriver");
    String url = "jdbc:firebirdsql:localhost/3050:/firebird/data/querydsl.fdb";
    return DriverManager.getConnection(url, "sysdba", "masterkey");
  }

  private static Connection getHSQL() throws SQLException, ClassNotFoundException {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:target/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }

  public static Connection getH2() throws SQLException, ClassNotFoundException {
    Class.forName("org.h2.Driver");
    String url = "jdbc:h2:./target/h2-test;LOCK_MODE=0;AUTO_SERVER=TRUE;MODE=legacy";
    return DriverManager.getConnection(url, "sa", "");
  }

  private static Connection getMySQL() throws SQLException, ClassNotFoundException {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/querydsl?useLegacyDatetimeCode=false";
    return DriverManager.getConnection(url, "querydsl", "querydsl");
  }

  private static Connection getOracle() throws SQLException, ClassNotFoundException {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    return DriverManager.getConnection(url, "querydsl", "querydsl");
  }

  private static Connection getPostgreSQL() throws ClassNotFoundException, SQLException {
    Class.forName("org.postgresql.Driver");
    String url = "jdbc:postgresql://localhost:5432/querydsl";
    return DriverManager.getConnection(url, "querydsl", "querydsl");
  }

  private static Connection getSQLServer() throws ClassNotFoundException, SQLException {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    String url =
        "jdbc:sqlserver://localhost:1433;databaseName=tempdb;sendTimeAsDatetime=false;trustServerCertificate=true";
    return DriverManager.getConnection(url, "sa", "Password1!");
  }

  private static Connection getCubrid() throws ClassNotFoundException, SQLException {
    Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
    String url = "jdbc:cubrid:localhost:30000:demodb:public::";
    return DriverManager.getConnection(url);
  }

  private static Connection getSQLite() throws SQLException, ClassNotFoundException {
    // System.setProperty("sqlite.purejava", "true");
    Class.forName("org.sqlite.JDBC");
    return DriverManager.getConnection("jdbc:sqlite:target/sample.db");
  }

  private static Connection getTeradata() throws SQLException, ClassNotFoundException {
    Class.forName("com.teradata.jdbc.TeraDriver");
    return DriverManager.getConnection("jdbc:teradata://teradata/dbc", "querydsl", "querydsl");
  }

  private static CreateTableClause createTable(SQLTemplates templates, String table) {
    return new CreateTableClause(connHolder.get(), new Configuration(templates), table);
  }

  public static void dropTable(SQLTemplates templates, String table) throws SQLException {
    new DropTableClause(connHolder.get(), new Configuration(templates), table).execute();
  }

  public static void dropType(Statement stmt, String type) throws SQLException {
    try {
      stmt.execute("drop type " + type);
    } catch (SQLException e) {
      if (!e.getMessage().contains("does not exist")) {
        throw e;
      }
    }
  }

  public static Statement getStatement() {
    return stmtHolder.get();
  }

  private static void createEmployeeTable(SQLTemplates templates) {
    createTable(templates, "EMPLOYEE")
        .column("ID", Integer.class)
        .notNull()
        .column("FIRSTNAME", String.class)
        .size(50)
        .column("LASTNAME", String.class)
        .size(50)
        .column("SALARY", Double.class)
        .column("DATEFIELD", Date.class)
        .column("TIMEFIELD", Time.class)
        .column("SUPERIOR_ID", Integer.class)
        .primaryKey("PK_EMPLOYEE", "ID")
        .foreignKey("FK_SUPERIOR", "SUPERIOR_ID")
        .references("EMPLOYEE", "ID")
        .execute();
  }

  public static Map<Integer, String> getSpatialData() {
    Map<Integer, String> m = new HashMap<>();
    // point
    m.put(1, "POINT (2 2)");
    m.put(2, "POINT (8 7)");
    m.put(3, "POINT (1 9)");
    m.put(4, "POINT (9 2)");
    m.put(5, "POINT (4 4)");
    // linestring
    m.put(6, "LINESTRING (30 10, 10 30)");
    m.put(7, "LINESTRING (30 10, 10 30, 40 40)");
    // polygon
    m.put(8, "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10), (20 30, 35 35, 30 20, 20 30))");
    m.put(9, "POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10), (20 30, 35 35, 30 20, 20 30))");
    // multipoint
    m.put(11, "MULTIPOINT (10 40, 40 30)");
    m.put(11, "MULTIPOINT (10 40, 40 30, 20 20, 30 10)");
    // multilinestring
    m.put(12, "MULTILINESTRING ((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10))");
    m.put(13, "MULTILINESTRING ((10 10, 20 20, 10 40))");
    // multipolygon
    m.put(14, "MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))");
    m.put(
        15,
        """
        MULTIPOLYGON (((40 40, 20 45, 45 30, 40 40)), \
        ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35), \
        (30 20, 20 15, 20 25, 30 20)))\
        """);

    // XXX POLYHEDRALSURFACE not supported

    /* GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10))
       CIRCULARSTRING(1 5, 6 2, 7 3)
       COMPOUNDCURVE(CIRCULARSTRING(0 0,1 1,1 0),(1 0,0 1))
       CURVEPOLYGON(CIRCULARSTRING(-2 0,-1 -1,0 0,1 -1,2 0,0 2,-2 0),(-1 0,0 0.5,1 0,0 1,-1 0))
       MULTICURVE((5 5,3 5,3 3,0 3),CIRCULARSTRING(0 0,2 1,2 2))
       TRIANGLE((0 0 0,0 1 0,1 1 0,0 0 0))
       TIN (((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)))
    */
    return m;
  }

  public static void initCubrid() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.CUBRID);
    // SQLTemplates templates = new MySQLTemplates();
    Connection c = getCubrid();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (cubridInited) {
      return;
    }

    // survey
    stmt.execute("drop table if exists SURVEY");
    stmt.execute(
        """
        create table SURVEY(ID int auto_increment(16693,2), \
        NAME varchar(30),\
        NAME2 varchar(30),\
        constraint suryey_pk primary key(ID))\
        """);
    stmt.execute("insert into SURVEY values (1,'Hello World','Hello');");

    // test
    stmt.execute("drop table if exists \"TEST\"");
    stmt.execute("create table \"TEST\"(NAME varchar(255))");
    try (PreparedStatement pstmt = c.prepareStatement("insert into \"TEST\" values(?)")) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    stmt.execute("drop table if exists EMPLOYEE");
    // createEmployeeTable(templates);
    stmt.execute(
        """
        create table EMPLOYEE ( \
        ID INT PRIMARY KEY AUTO_INCREMENT, \
        FIRSTNAME VARCHAR(50), \
        LASTNAME VARCHAR(50), \
        SALARY DECIMAL, \
        DATEFIELD DATE, \
        TIMEFIELD TIME, \
        SUPERIOR_ID INT, \
        CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID) \
        )\
        """);

    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    stmt.execute("drop table if exists TIME_TEST");
    stmt.execute("drop table if exists DATE_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    stmt.execute("drop table if exists NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 int)");

    // xml
    stmt.execute("drop table if exists XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    cubridInited = true;
  }

  public static void initDB2() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.DB2);
    SQLTemplates templates = new DB2Templates();
    Connection c = getDB2();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (db2Inited) {
      return;
    }

    // survey
    dropTable(templates, "SURVEY");
    stmt.execute(
        """
        create table SURVEY(\
        ID int generated by default as identity(start with 1, increment by 1), \
        NAME varchar(30),\
        NAME2 varchar(30))\
        """);
    stmt.execute("insert into SURVEY values (1,'Hello World','Hello')");

    // test
    dropTable(templates, "TEST");
    stmt.execute(CREATE_TABLE_TEST);
    stmt.execute("create index test_name on test(name)");
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    dropTable(templates, "EMPLOYEE");

    createEmployeeTable(templates);

    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    dropTable(templates, "TIME_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);

    dropTable(templates, "DATE_TEST");
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 smallint)");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    db2Inited = true;
  }

  public static void initDerby() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.DERBY);
    SQLTemplates templates = new DerbyTemplates();
    Connection c = getDerby();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (derbyInited) {
      return;
    }

    // types
    dropType(stmt, "price restrict");
    stmt.execute("create type price external name 'com.example.Price' language java");

    // survey
    dropTable(templates, "SURVEY");
    stmt.execute(
        """
        create table SURVEY(\
        ID int generated by default as identity(start with 1, increment by 1), \
        NAME varchar(30),\
        NAME2 varchar(30))\
        """);
    stmt.execute("insert into SURVEY values (1,'Hello World','Hello')");

    // test
    dropTable(templates, "TEST");
    stmt.execute(CREATE_TABLE_TEST);
    stmt.execute("create index test_name on test(name)");
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    dropTable(templates, "EMPLOYEE");

    createEmployeeTable(templates);

    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    dropTable(templates, "TIME_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);

    dropTable(templates, "DATE_TEST");
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 boolean)");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    derbyInited = true;
  }

  public static void initFirebird() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.FIREBIRD);
    SQLTemplates templates = new FirebirdTemplates();
    Connection c = getFirebird();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (firebirdInited) {
      return;
    }

    try {
      stmt.execute(
          """
          DECLARE EXTERNAL FUNCTION ltrim
             CSTRING(255)
             RETURNS CSTRING(255) FREE_IT
             ENTRY_POINT 'IB_UDF_ltrim' MODULE_NAME 'ib_udf'\
          """);
    } catch (SQLException e) {
      // do nothing
    }
    try {
      stmt.execute(
          """
          DECLARE EXTERNAL FUNCTION rtrim
             CSTRING(255) NULL
             RETURNS CSTRING(255) FREE_IT
             ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf'\
          """);
    } catch (SQLException e) {
      // do nothing
    }

    // survey
    dropTable(templates, "SURVEY");
    stmt.execute(
        """
        create table SURVEY(ID int primary key, \
        NAME varchar(30),\
        NAME2 varchar(30))\
        """);

    stmt.execute("insert into SURVEY values (1,'Hello World','Hello');");

    try {
      // stmt.execute("DROP TRIGGER survey_auto_id;");
      stmt.execute("DROP GENERATOR survey_gen_id;");
    } catch (SQLException e) {
      // do nothing
    }

    stmt.execute("CREATE GENERATOR survey_gen_id;");
    stmt.execute("SET GENERATOR survey_gen_id TO 30;");
    stmt.execute(
        """
        CREATE TRIGGER survey_auto_id FOR survey
        ACTIVE BEFORE INSERT POSITION 0
        AS
        BEGIN
        IF (NEW.id IS NULL) THEN
        NEW.id = GEN_ID(survey_gen_id,1);
        END \
        """);

    // test
    dropTable(templates, "TEST");
    stmt.execute(CREATE_TABLE_TEST);
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    dropTable(templates, "EMPLOYEE");
    // createEmployeeTable(templates);
    stmt.execute(
        """
        create table EMPLOYEE ( \
        ID INT PRIMARY KEY, \
        FIRSTNAME VARCHAR(50), \
        LASTNAME VARCHAR(50), \
        SALARY DECIMAL, \
        DATEFIELD DATE, \
        TIMEFIELD TIME, \
        SUPERIOR_ID INT, \
        CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID) \
        )\
        """);

    try {
      // stmt.execute("DROP TRIGGER employee_auto_id;");
      stmt.execute("DROP GENERATOR employee_gen_id;");
    } catch (SQLException e) {
      // do nothing
    }

    stmt.execute("CREATE GENERATOR employee_gen_id;");
    stmt.execute("SET GENERATOR employee_gen_id TO 30;");
    stmt.execute(
        """
        CREATE TRIGGER employee_auto_id FOR employee
        ACTIVE BEFORE INSERT POSITION 0
        AS
        BEGIN
        IF (NEW.id IS NULL) THEN
        NEW.id = GEN_ID(employee_gen_id,1);
        END \
        """);

    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    dropTable(templates, "TIME_TEST");
    dropTable(templates, "DATE_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 char(1))");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    firebirdInited = true;
  }

  public static void initH2() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.H2);
    SQLTemplates templates = new H2Templates();
    Connection c = getH2();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (h2Inited) {
      return;
    }

    stmt.execute("DROP ALIAS IF EXISTS H2GIS_SPATIAL");
    stmt.execute(
        "CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR \"org.h2gis.functions.factory.H2GISFunctions.load\"");
    stmt.execute("CALL H2GIS_SPATIAL();");

    // shapes
    dropTable(templates, "SHAPES");
    stmt.execute("create table SHAPES (ID int not null primary key, GEOMETRY geometry)");
    for (Map.Entry<Integer, String> entry : getSpatialData().entrySet()) {
      stmt.execute(
          "insert into SHAPES values("
              + entry.getKey()
              + ", ST_GeomFromText('"
              + entry.getValue()
              + "'))");
    }

    // qtest
    stmt.execute("drop table QTEST if exists");
    stmt.execute(
        "create table QTEST (ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  C1 int NULL)");

    // uuids
    stmt.execute("drop table if exists UUIDS");
    stmt.execute("create table UUIDS (FIELD uuid)");

    // survey
    stmt.execute("drop table SURVEY if exists");
    stmt.execute(CREATE_TABLE_SURVEY);
    stmt.execute("insert into SURVEY values (1, 'Hello World', 'Hello');");
    stmt.execute("alter table SURVEY alter column id int auto_increment");

    // test
    stmt.execute("drop table TEST if exists");
    stmt.execute(CREATE_TABLE_TEST);
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    stmt.execute("drop table EMPLOYEE if exists");
    createEmployeeTable(templates);
    stmt.execute("alter table EMPLOYEE alter column id int auto_increment");
    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    stmt.execute("drop table TIME_TEST if exists");
    stmt.execute("drop table DATE_TEST if exists");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 boolean)");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    h2Inited = true;
  }

  public static void initHSQL() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.HSQLDB);
    SQLTemplates templates = new HSQLDBTemplates();
    Connection c = getHSQL();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (hsqlInited) {
      return;
    }

    // arrays
    stmt.execute("drop table ARRAYTEST if exists");
    stmt.execute(
        """
        create table ARRAYTEST ( \
        ID bigint primary key, \
        INTEGERS integer array, \
        MYARRAY varchar(8) array)\
        """);

    // dual
    stmt.execute("drop table DUAL if exists");
    stmt.execute("create table DUAL ( DUMMY varchar(1) )");
    stmt.execute("insert into DUAL (DUMMY) values ('X')");

    // survey
    stmt.execute("drop table SURVEY if exists");
    // stmt.execute(CREATE_TABLE_SURVEY);
    stmt.execute(
        """
        create table SURVEY(\
        ID int generated by default as identity, \
        NAME varchar(30),\
        NAME2 varchar(30))\
        """);
    stmt.execute("insert into SURVEY values (1, 'Hello World', 'Hello')");

    // test
    stmt.execute("drop table TEST if exists");
    stmt.execute(CREATE_TABLE_TEST);
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    stmt.execute("drop table EMPLOYEE if exists");
    createEmployeeTable(templates);
    stmt.execute("alter table EMPLOYEE alter column id int generated by default as identity");
    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    stmt.execute("drop table TIME_TEST if exists");
    stmt.execute("drop table DATE_TEST if exists");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 boolean)");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    hsqlInited = true;
  }

  public static void initMySQL() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.MYSQL);
    // SQLTemplates templates = new MySQLTemplates();
    Connection c = getMySQL();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (mysqlInited) {
      return;
    }

    // shapes
    stmt.execute("drop table if exists SHAPES");
    stmt.execute("create table SHAPES (ID int not null primary key, GEOMETRY geometry)");
    for (Map.Entry<Integer, String> entry : getSpatialData().entrySet()) {
      stmt.execute(
          "insert into SHAPES values("
              + entry.getKey()
              + ", GeomFromText('"
              + entry.getValue()
              + "'))");
    }

    // survey
    stmt.execute("drop table if exists SURVEY");
    stmt.execute(
        """
        create table SURVEY(ID int primary key auto_increment, \
        NAME varchar(30),\
        NAME2 varchar(30))\
        """);
    stmt.execute("insert into SURVEY values (1,'Hello World','Hello');");

    // test
    stmt.execute("drop table if exists TEST");
    stmt.execute(CREATE_TABLE_TEST);
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    stmt.execute("drop table if exists EMPLOYEE");
    // createEmployeeTable(templates);
    stmt.execute(
        """
        create table EMPLOYEE ( \
        ID INT PRIMARY KEY AUTO_INCREMENT, \
        FIRSTNAME VARCHAR(50), \
        LASTNAME VARCHAR(50), \
        SALARY DECIMAL, \
        DATEFIELD DATE, \
        TIMEFIELD TIME, \
        SUPERIOR_ID INT, \
        CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID) \
        )\
        """);

    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    stmt.execute("drop table if exists TIME_TEST");
    stmt.execute("drop table if exists DATE_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    stmt.execute("drop table if exists NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 tinyint(1))");

    // xml
    stmt.execute("drop table if exists XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    mysqlInited = true;
  }

  public static void initOracle() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.ORACLE);
    SQLTemplates templates = new OracleTemplates();
    Connection c = getOracle();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (oracleInited) {
      return;
    }

    // types
    stmt.execute("create or replace type ssn_t as object (ssn_type char(11))");

    // survey
    dropTable(templates, "SURVEY");
    stmt.execute(
        """
        create table SURVEY (ID number(10,0), \
        NAME varchar(30 char),\
        NAME2 varchar(30 char))\
        """);

    try {
      stmt.execute("drop sequence survey_seq");
    } catch (SQLException e) {
      if (!e.getMessage().contains("sequence does not exist")) {
        throw e;
      }
    }

    stmt.execute("create sequence survey_seq");
    stmt.execute(
        """
        create or replace trigger survey_trigger
        before insert on survey
        for each row
        when (new.id is null)
        begin
          select survey_seq.nextval into :new.id from dual;
        end;
        """);

    stmt.execute("insert into SURVEY values (1,'Hello World','Hello')");

    // test
    dropTable(templates, "TEST");
    stmt.execute("create table TEST(name varchar(255))");
    String sql = "insert into TEST values(?)";
    PreparedStatement pstmt = c.prepareStatement(sql);
    for (int i = 0; i < TEST_ROW_COUNT; i++) {
      pstmt.setString(1, "name" + i);
      pstmt.addBatch();
    }
    pstmt.executeBatch();

    // employee
    dropTable(templates, "EMPLOYEE");
    stmt.execute(
        """
        create table EMPLOYEE ( \
        ID NUMBER(10,0), \
        FIRSTNAME VARCHAR2(50 CHAR), \
        LASTNAME VARCHAR2(50 CHAR), \
        SALARY DOUBLE PRECISION, \
        DATEFIELD DATE, \
        TIMEFIELD TIMESTAMP, \
        SUPERIOR_ID NUMBER(10,0), \
        CONSTRAINT PK_EMPLOYEE PRIMARY KEY(ID), \
        CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID)\
        )\
        """);

    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    dropTable(templates, "DATE_TEST");
    stmt.execute("create table date_test(date_test date)");

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 number(1,0))");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(COL XMLTYPE)");

    oracleInited = true;
  }

  public static void initPostgreSQL() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.POSTGRESQL);
    SQLTemplates templates = new PostgreSQLTemplates(true);
    // NOTE : unquoted identifiers are converted to lower case in PostgreSQL
    Connection c = getPostgreSQL();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (postgresqlInited) {
      return;
    }

    // shapes
    dropTable(templates, "SHAPES");
    //        stmt.execute("create table \"SHAPES\" (\"ID\" int not null primary key, \"GEOMETRY\"
    // geography(POINT,4326))");
    stmt.execute("create table \"SHAPES\" (\"ID\" int not null primary key)");
    stmt.execute("select AddGeometryColumn('SHAPES', 'GEOMETRY', -1, 'GEOMETRY', 2)");
    for (Map.Entry<Integer, String> entry : getSpatialData().entrySet()) {
      stmt.execute(
          "insert into \"SHAPES\" values(" + entry.getKey() + ", '" + entry.getValue() + "')");
    }

    // types
    dropType(stmt, "u_country");
    stmt.execute("create type u_country as enum ('Brazil', 'England', 'Germany')");

    dropType(stmt, "u_street_type");
    stmt.execute("create type u_street_type as (street VARCHAR(100), number VARCHAR(30))");

    // arrays
    dropTable(templates, "ARRAYTEST");
    stmt.execute(
        """
        create table "ARRAYTEST" (
        "ID" bigint primary key,
        "INTEGERS" integer[],
        "MYARRAY" varchar(8)[])\
        """);

    // uuids
    dropTable(templates, "UUIDS");
    stmt.execute("create table \"UUIDS\" (\"FIELD\" uuid)");

    // survey
    dropTable(templates, "SURVEY");
    try {
      stmt.execute("drop sequence SURVEY_SEQ");
    } catch (SQLException e) {
      if (!e.getMessage().contains("does not exist")) {
        throw e;
      }
    }
    stmt.execute("create sequence SURVEY_SEQ");
    stmt.execute(
        """
        create table "SURVEY"(\
        "ID" int DEFAULT NEXTVAL('SURVEY_SEQ'), \
        "NAME" varchar(30), "NAME2" varchar(30))\
        """);
    stmt.execute("insert into \"SURVEY\" values (1, 'Hello World', 'Hello')");

    // test
    dropTable(templates, "TEST");
    stmt.execute(quote(CREATE_TABLE_TEST, "TEST", "NAME"));
    String sql = quote(INSERT_INTO_TEST_VALUES, "TEST");
    try (PreparedStatement pstmt = c.prepareStatement(sql)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    // stmt.execute("drop table employee if exists");
    dropTable(templates, "EMPLOYEE");
    createEmployeeTable(templates);
    addEmployees(
        """
        insert into "EMPLOYEE" \
        ("ID", "FIRSTNAME", "LASTNAME", "SALARY", "DATEFIELD", "TIMEFIELD", "SUPERIOR_ID") \
        values (?,?,?,?,?,?,?)\
        """);

    // date_test and time_test
    dropTable(templates, "TIME_TEST");
    dropTable(templates, "DATE_TEST");
    stmt.execute(quote(CREATE_TABLE_TIMETEST, "TIME_TEST"));
    stmt.execute(quote(CREATE_TABLE_DATETEST, "DATE_TEST"));

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table \"NUMBER_TEST\"(\"COL1\" boolean)");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table \"XML_TEST\"(\"COL\" XML)");

    postgresqlInited = true;
  }

  public static void initSQLite() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.SQLITE);
    //        SQLTemplates templates = new SQLiteTemplates();
    Connection c = getSQLite();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (sqliteInited) {
      return;
    }

    // qtest
    stmt.execute("drop table if exists QTEST");
    stmt.execute("create table QTEST (ID int IDENTITY(1,1) NOT NULL,  C1 int NULL)");

    // survey
    stmt.execute("drop table if exists SURVEY");
    stmt.execute(
        """
        create table SURVEY(ID int auto_increment, \
        NAME varchar(30),\
        NAME2 varchar(30),\
        constraint survey_pk primary key(ID))\
        """);
    stmt.execute("insert into SURVEY values (1,'Hello World','Hello');");

    // test
    stmt.execute("drop table if exists TEST");
    stmt.execute(CREATE_TABLE_TEST);
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    stmt.execute("drop table if exists EMPLOYEE");
    stmt.execute(
        """
        create table EMPLOYEE ( \
        ID INT AUTO_INCREMENT, \
        FIRSTNAME VARCHAR(50), \
        LASTNAME VARCHAR(50), \
        SALARY DECIMAL, \
        DATEFIELD DATE, \
        TIMEFIELD TIME, \
        SUPERIOR_ID INT, \
        CONSTRAINT PK_EMPLOYEE PRIMARY KEY(ID),\
        CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID) \
        )\
        """);
    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    stmt.execute("drop table if exists TIME_TEST");
    stmt.execute("drop table if exists DATE_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    stmt.execute("drop table if exists NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 integer)");

    // xml
    stmt.execute("drop table if exists XML_TEST");
    stmt.execute("create table XML_TEST(COL varchar(128))");

    sqliteInited = true;
  }

  public static void initSQLServer() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.SQLSERVER);
    SQLTemplates templates = new SQLServerTemplates();
    Connection c = getSQLServer();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (sqlServerInited) {
      return;
    }

    dropTable(templates, "SHAPES");
    stmt.execute("create table SHAPES (ID int not null primary key, GEOMETRY geometry)");
    for (Map.Entry<Integer, String> entry : getSpatialData().entrySet()) {
      stmt.execute(
          "insert into SHAPES values("
              + entry.getKey()
              + ", geometry::STGeomFromText('"
              + entry.getValue()
              + "', 0))");
    }

    // survey
    dropTable(templates, "SURVEY");
    stmt.execute("create table SURVEY(ID int, NAME varchar(30), NAME2 varchar(30))");
    stmt.execute("insert into SURVEY values (1, 'Hello World', 'Hello')");

    // test
    dropTable(templates, "TEST");
    stmt.execute(CREATE_TABLE_TEST);
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    dropTable(templates, "EMPLOYEE");
    createEmployeeTable(templates);
    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    dropTable(templates, "TIME_TEST");
    dropTable(templates, "DATE_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(col1 bit)");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(COL XML)");

    sqlServerInited = true;
  }

  public static void initTeradata() throws SQLException, ClassNotFoundException {
    targetHolder.set(Target.TERADATA);
    SQLTemplates templates = new TeradataTemplates();
    Connection c = getTeradata();
    connHolder.set(c);
    Statement stmt = c.createStatement();
    stmtHolder.set(stmt);

    if (teradataInited) {
      return;
    }

    String identity = "GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1)";

    // shapes
    dropTable(templates, "SHAPES");
    stmt.execute("create table SHAPES (ID int not null primary key, GEOMETRY ST_GEOMETRY)");
    for (Map.Entry<Integer, String> entry : getSpatialData().entrySet()) {
      stmt.execute("insert into SHAPES values(" + entry.getKey() + ", '" + entry.getValue() + "')");
    }

    // qtest
    dropTable(templates, "QTEST");
    stmt.execute("create table QTEST (ID int " + identity + " NOT NULL, C1 int NULL)");

    // survey
    dropTable(templates, "SURVEY");
    stmt.execute(
        "create table SURVEY(ID int " + identity + ", NAME varchar(30), NAME2 varchar(30))");
    stmt.execute("insert into SURVEY values (1, 'Hello World', 'Hello');");

    // test
    dropTable(templates, "TEST");
    stmt.execute(CREATE_TABLE_TEST);
    try (PreparedStatement pstmt = c.prepareStatement(INSERT_INTO_TEST_VALUES)) {
      for (int i = 0; i < TEST_ROW_COUNT; i++) {
        pstmt.setString(1, "name" + i);
        pstmt.addBatch();
      }
      pstmt.executeBatch();
    }

    // employee
    dropTable(templates, "EMPLOYEE");
    stmt.execute(
        """
        create table EMPLOYEE (
        ID INTEGER NOT NULL PRIMARY KEY,\s
        FIRSTNAME VARCHAR(100),
        LASTNAME VARCHAR(100),
        SALARY DOUBLE PRECISION,
        DATEFIELD DATE,
        TIMEFIELD TIME,
        SUPERIOR_ID INTEGER,
        CONSTRAINT FK_SUPERIOR FOREIGN KEY(SUPERIOR_ID) REFERENCES EMPLOYEE(ID))\
        """);
    addEmployees(INSERT_INTO_EMPLOYEE);

    // date_test and time_test
    dropTable(templates, "TIME_TEST");
    dropTable(templates, "DATE_TEST");
    stmt.execute(CREATE_TABLE_TIMETEST);
    stmt.execute(CREATE_TABLE_DATETEST);

    // numbers
    dropTable(templates, "NUMBER_TEST");
    stmt.execute("create table NUMBER_TEST(ID int " + identity + " NOT NULL, col1 int)");

    // xml
    dropTable(templates, "XML_TEST");
    stmt.execute("create table XML_TEST(ID int " + identity + " NOT NULL, COL varchar(128))");

    teradataInited = true;
  }

  static void addEmployee(
      String sql, int id, String firstName, String lastName, double salary, int superiorId)
      throws SQLException {
    PreparedStatement stmt = connHolder.get().prepareStatement(sql);
    stmt.setInt(1, id);
    stmt.setString(2, firstName);
    stmt.setString(3, lastName);
    stmt.setDouble(4, salary);
    stmt.setDate(5, Constants.date);
    stmt.setTime(6, Constants.time);
    if (superiorId <= 0) {
      stmt.setNull(7, Types.INTEGER);
    } else {
      stmt.setInt(7, superiorId);
    }
    stmt.execute();
    stmt.close();
  }

  private static void addEmployees(String sql) throws SQLException {
    addEmployee(sql, 1, "Mike", "Smith", 160000, -1);
    addEmployee(sql, 2, "Mary", "Smith", 140000, -1);

    // Employee under Mike
    addEmployee(sql, 10, "Joe", "Divis", 50000, 1);
    addEmployee(sql, 11, "Peter", "Mason", 45000, 1);
    addEmployee(sql, 12, "Steve", "Johnson", 40000, 1);
    addEmployee(sql, 13, "Jim", "Hood", 35000, 1);

    // Employee under Mike
    addEmployee(sql, 20, "Jennifer", "Divis", 60000, 2);
    addEmployee(sql, 21, "Helen", "Mason", 50000, 2);
    addEmployee(sql, 22, "Daisy", "Johnson", 40000, 2);
    addEmployee(sql, 23, "Barbara", "Hood", 30000, 2);
  }

  private static String quote(String sql, String... identifiers) {
    String rv = sql;
    for (String id : identifiers) {
      rv = rv.replace(id, "\"" + id + "\"");
    }
    return rv;
  }

  private Connections() {}
}
